package com.sam.security.dao.impl;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.sam.core.dao.jdbc.BaseJdbcTemplate;
import com.sam.core.domain.Page;
import com.sam.security.dao.SecurityDao;
import com.sam.security.domain.AsyncTreeNode;
import com.sam.security.domain.Authority;
import com.sam.security.domain.Menu;
import com.sam.security.domain.Permission;
import com.sam.security.domain.Resource;
import com.sam.security.domain.Role;
import com.sam.security.domain.User;

/**
 * 权限管理模块Dao实现类
 * @project EMan
 * @author Sam
 * @version
 * @date Feb 17, 2009 10:37:05 AM
 */
@Repository("securityJdbcDao")
@SuppressWarnings("unchecked")
public class SecurityDaoImpl extends BaseJdbcTemplate implements SecurityDao {

	public void addMenu(Menu menu) {
		update(
				"INSERT INTO Menu (menuID, menuName, menuLink, menuDescription, addTime) VALUES (:id, :name, :link,:description,getDate())",
				menu);
	}

	public void addPermission(Permission permission) {
		update("INSERT INTO UserFunctionOfMenu "
				+ "(userFunctionName, menuID, userFunctionDescription, addTime) "
				+ "VALUES (:name, :menuId, :description, getDate())", permission);
	}

	public void addPermissionResource(String permissionId, String resourceId) {
		update(
				"INSERT INTO t_XTKF_Permission_SecurityResource (permissionId, resourceId) VALUES (?, ?)",
				permissionId, resourceId);
	}

	public void addResource(Resource resource) {
		update(
				"INSERT INTO t_XTKF_SecurityResource (resourceName,resourceString,resourceType) VALUES(:resourceName, :resourceString,:resourceType)",
				resource);
	}

	public void addRole(Role role) {
		update("INSERT INTO dbo.Role ( roleName, roleDescription ) VALUES (:name,:description)",
				role);

	}

	public void addRolePermission(String roleId, String userFunctionName) {
		jdbcUpdate("INSERT INTO UserFunctionOfRole (roleID, userFunctionName) VALUES (?, ?)",
				roleId, userFunctionName);
	}

	public void addRolePermissionAll(String roleId, String menuId) {
		jdbcUpdate("INSERT INTO UserFunctionOfRole SELECT " + Integer.parseInt(roleId)
				+ " AS roleId, userFunctionName FROM UserFunctionOfMenu WHERE (menuID like ?)",
				menuId + '%');
	}

	public void deleteMenu(String menuId) {
		jdbcUpdate("DELETE FROM Menu WHERE (menuID LIKE ?)", menuId + '%');
	}

	public void deletePermission(String permissionId) {
		jdbcUpdate("DELETE FROM UserFunctionOfMenu WHERE (userFunctionID = ?)", permissionId);
	}

	public void deletePermissionResource(String permissionId) {
		jdbcUpdate("DELETE FROM t_XTKF_Permission_SecurityResource WHERE (permissionId = ?)",
				permissionId);
	}

	public void deleteReource(String resourceId) {
		jdbcUpdate("DELETE FROM t_XTKF_SecurityResource WHERE (resourceId = ?)", resourceId);
	}

	public void deleteRole(String roleId) {
		jdbcUpdate("DELETE FROM Role WHERE (roleID = ?)", roleId);
	}

	public void deleteRolePermission(String roleId, String menuId) {
		jdbcUpdate(
				"DELETE FROM UserFunctionOfRole WHERE (roleID = ?) AND (userFunctionName IN (SELECT userFunctionName FROM UserFunctionOfMenu WHERE menuId like ?))",
				roleId, menuId + '%');
	}

	public void filterResource(Resource resource, Page<Resource> page) {
		page.setOrder("resourceId");
		page(
				"SELECT resourceId,resourceName,resourceString,resourceType FROM t_XTKF_SecurityResource  WHERE resourceName like :resourceName AND resourceString like :resouceString AND resourceType like :resourceType",
				resource, Resource.class, page);
	}

	public List<Resource> loadResource() {
		return query(
				"SELECT resourceId,resourceName,resourceString,resourceType FROM t_XTKF_SecurityResource",
				Resource.class);
	}

	public void loadResource(Page page) {
		page.setOrder("resourceId");
		page(
				"SELECT resourceId,resourceName,resourceString,resourceType FROM t_XTKF_SecurityResourcde",
				Resource.class, page);
	}

	public void loadResource(Resource resource, Page page) {
		page.setOrder("resourceId");
		page(
				"SELECT resourceId,resourceName,resourceString,resourceType FROM t_XTKF_SecurityResource  WHERE resourceID=:resourceId",
				resource, Resource.class, page);
	}

	public List<Resource> loadResourceById(int resourceId) {
		return query(
				"SELECT resourceId,resourceName,resourceString,resourceType FROM t_XTKF_SecurityResource  WHERE resourceId=?",
				Resource.class, resourceId);
	}

	public List<Role> loadRole(Page page) {
		page.setOrder("ID");
		page("SELECT roleID AS id, roleName AS name, roleDescription AS description FROM Role",
				Role.class, page);
		return null;
	}

	public List<Role> loadRole(String roleId) {
		return query(
				"SELECT roleID AS id, roleName AS name, roleDescription AS description FROM Role WHERE (roleID = ?)",
				Role.class, roleId);
	}

	public List<Menu> queryAllMenu() {
		return query(
				"SELECT menuID AS id,left(menuID,len(menuID)-4) AS parentId, menuName AS name, menuLink AS link,menuDescription AS description FROM Menu ORDER BY Menu.menuID",
				Menu.class);
	}

	public List<Authority> queryAuthsByUsername(String username) {
		return query(
				"SELECT DISTINCT UserFunctionOfMenu.userFunctionName AS name, UserFunctionOfMenu.userFunctionName AS displayName "
						+ "FROM Login INNER JOIN "
						+ "LoginOfRole ON Login.loginID = LoginOfRole.loginID INNER JOIN "
						+ "UserFunctionOfRole ON LoginOfRole.roleID = UserFunctionOfRole.roleID INNER JOIN "
						+ "UserFunctionOfMenu ON "
						+ "UserFunctionOfRole.userFunctionName = UserFunctionOfMenu.userFunctionName "
						+ "WHERE (Login.loginID = ?) AND (UserFunctionOfMenu.userFunctionName LIKE 'AUTH_%')",
				Authority.class, username);
	}

	public List<Permission> queryCheckedPermission(String roleId, String menuId) {
		return query(
				"SELECT UserFunctionOfMenu.userFunctionID AS id,UserFunctionOfMenu.userFunctionName AS name, UserFunctionOfMenu.menuId "
						+ "FROM UserFunctionOfMenu INNER JOIN "
						+ "UserFunctionOfRole ON "
						+ "UserFunctionOfMenu.userFunctionName = UserFunctionOfRole.userFunctionName "
						+ "WHERE (UserFunctionOfMenu.menuID = ?) AND (UserFunctionOfRole.roleID = ?)",
				Permission.class, menuId, roleId);
	}

	public String queryLastSubMenuId(String parentId) {
		return (String) queryForObject("SELECT MAX(menuID) FROM Menu WHERE (menuID LIKE ?)",
				String.class, parentId);
	}

	public Menu queryMenuInfo(String id) {
		return (Menu) queryForObject(
				"SELECT menuID AS id,left(menuID,len(menuID)-4) AS parentId, menuName AS name, menuLink AS link,menuDescription AS description FROM Menu WHERE (menuID = ?)",
				Menu.class, id);
	}

	public Permission queryPermissionInfo(String id) {
		return (Permission) queryForObject(
				"SELECT userFunctionName AS name, userFunctionID AS id, menuID AS menuId FROM UserFunctionOfMenu WHERE (userFunctionID = ?)",
				Permission.class, id);
	}

	public List<Resource> queryPermissionResource(String permissionId) {
		return query("SELECT s.resourceId, s.resourceName, s.resourceString,s.resourceType "
				+ "FROM t_XTKF_Permission_SecurityResource ps INNER JOIN "
				+ "t_XTKF_SecurityResource s ON ps.resourceId = s.resourceId "
				+ "WHERE (ps.permissionId = ?)", Resource.class, permissionId);
	}

	public List<Map<String, Object>> queryRequestMap() {
		return queryForListMap(
				"SELECT t_XTKF_SecurityResource.resourceString,UserFunctionOfMenu.userFunctionName, "
						+ "CAST(UserFunctionOfMenu.userFunctionID AS varchar(50)) AS userFunctionID FROM t_XTKF_SecurityResource INNER JOIN "
						+ "t_XTKF_Permission_SecurityResource ON "
						+ "t_XTKF_SecurityResource.resourceId = t_XTKF_Permission_SecurityResource.resourceId "
						+ "INNER JOIN UserFunctionOfMenu ON "
						+ "t_XTKF_Permission_SecurityResource.permissionId = UserFunctionOfMenu.userFunctionID ",
				null);
	}

	public List<AsyncTreeNode> querySubMenus(String id) {
		return query(
				"SELECT menuID AS id, menuName AS text, menuLink AS link,(case when (exists(select m.menuID from Menu as m where m.menuID like Menu.menuID + '%' and m.menuID <> Menu.menuID))then 0 else 1 end) AS leaf,'' AS icon,(case when (exists(select u.menuID from UserFunctionOfMenu as u where u.menuID = Menu.menuID))then 1 else 0 end) AS type FROM Menu WHERE (menuID LIKE ?)",
				AsyncTreeNode.class, id + "____");
	}

	public List<AsyncTreeNode> querySubMenusForPermission(String id) {
		return query(
				"SELECT menuID AS id, menuName AS text, menuLink AS link,0 AS leaf,'' AS icon,(case when (exists(select u.menuID from UserFunctionOfMenu as u where u.menuID = Menu.menuID))then 1 else 0 end) AS type FROM Menu WHERE (menuID LIKE ?)",
				AsyncTreeNode.class, id + "____");
	}

	public List<Permission> queryUncheckedPermission(String roleId, String menuId) {
		return query(
				"SELECT UserFunctionOfMenu.userFunctionID AS id,UserFunctionOfMenu.userFunctionName AS name, UserFunctionOfMenu.menuId FROM UserFunctionOfMenu "
						+ "WHERE (menuID = ?) AND (userFunctionName NOT IN "
						+ "(SELECT userFunctionName FROM UserFunctionOfRole WHERE roleID = ?))",
				Permission.class, menuId, roleId);
	}

	public User queryUserByUsername(String username) {
		return (User) queryForObject(
				"SELECT Login.loginPassword AS password, Login.loginID AS username FROM Login WHERE (Login.loginID = ?)",
				User.class, username);
	}

	public List<AsyncTreeNode> queryUserFunctions(String id) {
		return query(
				"SELECT userFunctionName AS text, userFunctionID AS id, '' AS link,1 AS leaf,'' AS icon,'2' AS type FROM UserFunctionOfMenu WHERE (menuID = ?)",
				AsyncTreeNode.class, id);
	}

	public List<Resource> searchResource(String search) {
		return query(
				"SELECT resourceId,resourceName,resourceString,resourceType FROM t_XTKF_SecurityResource  WHERE resourceName like ? OR resourceString like ? ",
				Resource.class, '%' + search + '%', '%' + search + '%');
	}

	public void searchResource(String query, Page page) {
		page.setOrder("resourceId");
		page(
				"SELECT resourceId,resourceName,resourceString,resourceType FROM t_XTKF_SecurityResource  WHERE resourceName like :query OR resourceString like :query OR resourceType like :query",
				"query", query, Resource.class, page);
	}

	public List<Role> searchRole(String query, Page page) {
		return query(
				"SELECT roleID AS id, roleName AS name, roleDescription AS description FROM Role WHERE (roleName like ?)",
				Role.class, '%' + query + '%');
	}

	public void updateMenu(Menu menu) {
		update(
				"UPDATE Menu SET menuName = :name, menuLink = link, menuDescription = :description WHERE (menuID = :id)",
				menu);
	}

	public void updatePermission(Permission permission) {
		update(
				"UPDATE UserFunctionOfMenu SET userFunctionName = :name, userFunctionDescription = :description WHERE (userFunctionID = :id)",
				permission);
	}

	public void updatePerson(final String[] ids) {
		batchDelete("DELETE FROM t_XTKF_SecurityResource WHERE (resourceId = ?)", ids);
	}

	public void updateReource(Resource resource) {
		update(
				"UPDATE t_XTKF_SecurityResource SET resourceName=:resourceName,resourceString=:resourceString,resourceType=:resourceType WHERE resourceId=:resourceId",
				resource);
	}

	public void updateRole(Role role) {
		update(
				"UPDATE Role SET roleName = :name, roleDescription = :description WHERE (roleID = :id)",
				role);

	}
}
